from backend.database.base_db import BaseDB
from backend.database.base_db import BaseDB
from backend.models.order import Order

class OrderDB(BaseDB):
    def __init__(self):
        super().__init__('order')
    
    def get_order_by_id(self, order_id):
        query = "SELECT * FROM `order` WHERE order_id = %s"
        result = self.fetch_one(query, (order_id,))
        return Order.from_dict(result) if result else None
    
    def get_orders_by_user_id(self, user_id):
        query = "SELECT * FROM `order` WHERE user_id = %s ORDER BY order_time DESC"
        results = self.fetch_all(query, (user_id,))
        return [Order.from_dict(result) for result in results]
    
    def get_orders_by_status(self, status):
        query = "SELECT * FROM `order` WHERE order_status = %s ORDER BY order_time DESC"
        results = self.fetch_all(query, (status,))
        return [Order.from_dict(result) for result in results]
    
    def get_all_orders(self):
        query = "SELECT * FROM `order` ORDER BY order_time DESC"
        results = self.fetch_all(query)
        return [Order.from_dict(result) for result in results]
    
    def add_order(self, order):
        data = order.to_dict()
        # 移除可能存在的None值
        data = {k: v for k, v in data.items() if v is not None}
        # 如果有order_id，也移除，让数据库自动生成
        if 'order_id' in data:
            del data['order_id']
        
        # 确保order_time是字符串格式
        if 'order_time' in data and hasattr(data['order_time'], 'strftime'):
            data['order_time'] = data['order_time'].strftime('%Y-%m-%d %H:%M:%S')
        
        # 由于order是MySQL保留关键字，需要特殊处理insert方法
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO `order` ({columns}) VALUES ({placeholders})"
        
        cursor = self.execute_query(query, tuple(data.values()))
        last_id = cursor.lastrowid
        cursor.close()
        return last_id
    
    def update_order(self, order):
        data = order.to_dict()
        order_id = data.pop('order_id', None)
        if order_id is None:
            raise ValueError("订单ID不能为空")
        
        # 移除可能存在的None值
        data = {k: v for k, v in data.items() if v is not None}
        
        # 确保order_time是字符串格式
        if 'order_time' in data and hasattr(data['order_time'], 'strftime'):
            data['order_time'] = data['order_time'].strftime('%Y-%m-%d %H:%M:%S')
        
        return self.update(data, f"order_id = {order_id}")
    
    def update_order_status(self, order_id, status):
        query = "UPDATE `order` SET order_status = %s WHERE order_id = %s"
        cursor = self.execute_query(query, (status, order_id))
        affected_rows = cursor.rowcount
        cursor.close()
        return affected_rows > 0
    
    def delete_order(self, order_id):
        return self.delete(f"order_id = {order_id}")
    
    def get_order_count_by_status(self, status):
        query = "SELECT COUNT(*) as count FROM `order` WHERE order_status = %s"
        result = self.fetch_one(query, (status,))
        return result['count'] if result else 0